import pandas as pd
import os

p_path = os.getcwd()
dir_path = p_path + "/处理结果/"

shtk_list = pd.read_excel(dir_path + "1.售后退款.xlsx")
yfsr_list = pd.read_excel(dir_path + "1.运费收入.xlsx")
yftk_list = pd.read_excel(dir_path + "1.运费退款.xlsx")

print(f'售后退款数据：{len(shtk_list)} 条')
print(f'运费收入数据：{len(yfsr_list)} 条')
print(f'运费退款数据：{len(yftk_list)} 条')

sale_list = pd.read_excel(f"{p_path}/处理结果/5.处理过的结算数据.xlsx")
print(f'TEMU结算数据：{len(sale_list)} 条')

shtk_data = shtk_list.groupby(['订单编号', '子订单号', '币种'])['售后退款金额'].sum().reset_index()
shtk_data.rename(columns={"币种":"售后退款币种"},inplace=True)
shtk_data["售后退款金额"]=-shtk_data["售后退款金额"]
# a = shtk_data.iloc[200:300]
# shtk_data.to_excel(f"{p_path}/处理结果/6.售后退款.xlsx", index=False)

# 合并运费收入和运费退款
srtk_list = pd.merge(yfsr_list, yftk_list, how='left', left_on=["订单编号"],
                       right_on=["订单编号"])
srtk_list.drop(['店铺_x','账务时间_x','店铺_y','账务时间_y'], axis=1, inplace=True)
srtk_list.rename(columns={"币种_x":"运费收入币种","币种_y":"运费退款币种"},inplace=True)
srtk_list["运费退款"]=-srtk_list["运费退款"]
# result_list.to_excel(f"{p_path}/处理结果/6.运费收入和退款.xlsx", index=False)
# 合并结算数据和售后退款
result_list1 = pd.merge(sale_list, shtk_data, how='left', left_on=["订单编号","子订单号"],
                       right_on=["订单编号","子订单号"])

# 合并运费进结算数据
result_list2 = pd.merge(result_list1, srtk_list, how='left', left_on=["订单编号"],
                       right_on=["订单编号"])

# print(result_list2)
result_list2.to_excel(f"{p_path}/处理结果/7.最终的结算数据.xlsx", index=False)
print(f'合并之后的数据：{len(result_list2)} 条')
